import pymysql as mysql
import pandas as pd
import datetime
today = datetime.datetime.now().strftime('%Y-%m-%d')
host = "localhost"
user = "s"
passwd = "s"
#passwd = "sdh478174277"
#passwd = "songdonghui"
db = "brfdb"
conn = mysql.connect(host=host,user=user,password=passwd,database=db)
#df_base = pd.read_excel("D:/DeskTop/brf-db/model/blood.xlsx")
df_base = pd.read_excel("~/brfDB/brf-db/model/blood.xlsx")
df = df_base.astype(object).where(pd.notnull(df_base),None)
all_index = {
    'name': df.loc[1][2],
    'sex' : df.loc[1][6],
    'age' : df.loc[1][12],
    'date' : df.loc[1][16],
    'xt' : df.loc[2][2],
    'form' : df.loc[2][6],
    'doctor' : df.loc[2][12],
    'description' : df.loc[3][2],
    'wbc' : df.loc[5][4],
    'l' : df.loc[6][4],
    'm' : df.loc[7][4],
    'g' : df.loc[8][4],
    'lpre' : df.loc[9][4],
    'mpre' : df.loc[10][4],
    'gpre' : df.loc[11][4],
    'hgb' : df.loc[12][4],
    'rbc' : df.loc[13][4],
    'hct' : df.loc[14][4],
    'mcv' : df.loc[5][16],
    'mch' : df.loc[6][16],
    'mchc': df.loc[7][16],
    'rdw_cv' : df.loc[8][16],
    'rdw_sd' : df.loc[9][16],
    'plt' : df.loc[10][16],
    'mpv' : df.loc[11][16],
    'pdw' : df.loc[12][16],
    'pct' : df.loc[13][16],
    'ppre' : df.loc[14][16]
}
sql = "INSERT INTO blood VALUES(id,'{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format \
    (all_index['name'],all_index['sex'],all_index['age'],today,all_index['xt'], \
    all_index['form'],all_index['doctor'],all_index['description'],all_index['wbc'], \
    all_index['l'],all_index['m'],all_index['g'],all_index['lpre'],all_index['mpre'],all_index['gpre'], \
    all_index['hgb'],all_index['rbc'],all_index['hct'],all_index['mcv'],all_index['mch'],all_index['mchc'], \
    all_index['rdw_cv'],all_index['rdw_sd'],all_index['plt'],all_index['mpv'],all_index['pdw'],all_index['pct'], \
    all_index['ppre'] \
    )
cursor = conn.cursor()
#cursor.execute("./tools.sql")
try:
    cursor.execute(sql)
    data = cursor.fetchall()
    print(data)
    conn.commit()
except:
    conn.rollback()
cursor.close()
conn.close()